OpenEdge Development:<br />Programming Interfaces Reporting information from multiple tables

Each order has one or many order lines, each of which relates to a single inventory item. Accounting needs to pull information from the Order-Line and Item tables to get specific information about what was ordered. One more FOR EACH block, inside the existing block, completes the structure.

Once the procedure gathers all the information, accounting needs the procedure to calculate total prices. Nested FOR EACH blocks create sorted groups of information similar to break groups. Therefore, you can use the aggregate phrase to calculate total prices.

This code fragment comes from i-10-08.p and shows the completed structure of nested FOR EACH blocks:

i-10-08.p
. . . FOR EACH Customer FIELDS (Balance Credit-Limit Contact Cust-Num) WHERE Balance >= (Credit-Limit * .85) WITH STREAM-IO: DISPLAY Name FORMAT "x(20)" Contact FORMAT "x(15)" Balance Credit-Limit WITH NO-BOX. /1/ FOR EACH Order FIELDS (Cust-Num Order-Num Order-Date Ship-Date Promise-Date) WHERE Order.Cust-Num = Customer.Cust-Num WITH STREAM-IO: DISPLAY Order-Num Order-Date Ship-Date Promise-Date SKIP(1) WITH 2 COLUMNS. FOR EACH Order-Line FIELDS (Order-Num Item-Num Qty) WHERE Order-Line.Order-Num = Order.Order-Num WITH STREAM-IO: /2/ FIND Item WHERE Item.Item-Num = Order-Line.Item-Num. DISPLAY Qty Order-Line.Item-Num Item-Name FORMAT "x(13)" Item.Price LABEL "Unit Price" /3/ Item.Price * Qty (TOTAL) /4/ LABEL "Price" FORMAT "$zzz,zz9.99 CR" WITH NO-BOX. END. END. END. . . .

i-10-08.p

                .
                .
                .
        FOR EACH Customer FIELDS (Balance Credit-Limit Contact Cust-Num)
            WHERE Balance >= (Credit-Limit * .85) 
            WITH STREAM-IO:
            DISPLAY Name  FORMAT "x(20)" Contact  FORMAT "x(15)" 
                Balance Credit-Limit WITH NO-BOX.
                
/*1*/       FOR EACH Order FIELDS (Cust-Num Order-Num Order-Date Ship-Date
                Promise-Date) WHERE Order.Cust-Num = Customer.Cust-Num
                WITH STREAM-IO:
                DISPLAY Order-Num Order-Date Ship-Date 
                    Promise-Date SKIP(1) WITH 2 COLUMNS.
                    
                FOR EACH Order-Line FIELDS (Order-Num Item-Num Qty)
                    WHERE Order-Line.Order-Num =
                    Order.Order-Num WITH STREAM-IO:
/*2*/               FIND Item WHERE Item.Item-Num = Order-Line.Item-Num.
                    DISPLAY Qty Order-Line.Item-Num 
                        Item-Name FORMAT "x(13)"
                        Item.Price LABEL "Unit Price"
/*3*/                    Item.Price * Qty (TOTAL) 
/*4*/                  LABEL "Price" FORMAT "$zzz,zz9.99 CR" WITH NO-BOX.
                END.
            END.
        END.    
                .
                .
                .

The WHERE clause of the third FOR EACH relates the Order-Line table back to the Order table.

For each Order-Line, there is a single Item record that contains information about the ordered item. A simple FIND statement retrieves this information.

Here, the report totals the result of an expression. Notice the absence of the BY break group syntax.

The FORMAT option here specifies a fairly complex format string. The result displays a leading dollar sign ($), suppresses the leading zeroes (z), and displays the credit symbol (CR) when the result is a negative value.